Discussion thread for official Zabbix Template DB PostgreSQL

[jukka]

Is it possible to get this official postgresql template for 4.0LTS? I'd like to run LTS versions of server.

Thanks, Jukka.

[max.ch.88]

Is it possible to get this official postgresql template for 4.0LTS? I'd like to run LTS versions of server.

Thanks, Jukka.

The official postgresql template is only available for version 4.2 and later.
Maybe you will find something helpful on the web page https://www.zabbix.com/integrations/postgresql.

[Zillah]

Hi
I have one problem:

16444:20200212:162213.037 error reason for "zbx-pgdb:pgsql.ping.time["{$PG.HOST}","{$PG.PORT}","{$PG.USER}","{$PG.DB}"]" changed: Preprocessi
ng failed for: 1.Время: 0,222 мс
1. Failed: cannot perform regular expression "Time:\s(\d+\.\d+)\sms" match for value of type "string": pattern does not match

Can i fix it?

UPD
Ok i'am found: https://support.zabbix.com/browse/ZBX-17169

[Zillah]

One more problem:

zabbix_get -s 127.0.0.1 -k pgsql.ping.time["127.0.0.1","5432","zbx_monitor","zabbix"]
1
Time: 1.528 ms

But the trigger notifies:

PostgreSQL: Response too long (over 1s)

[nti]

Hi,

Ok never mind i got it all working, problem was that my pg_isready was returning values in polish not in English

How do you solve this?!? I have the same problem with German and can't figure it out. I think it would be better to evaluate the exit code from pg_isready than a string which maybe not in English.

[kepperta]

How do you solve this?!? I have the same problem with German and can't figure it out. I think it would be better to evaluate the exit code from pg_isready than a string which maybe not in English.

U have to edit template manualy, find item named "Status: Ping" then in Preprocessing change paremeter, in my case it looks like this :

return value.search(/akceptowanie połączeń/)>0 ? 1 : 0

It would be mutch better if it was checking exit code not string

[Doomguy]

One more problem:

zabbix_get -s 127.0.0.1 -k pgsql.ping.time["127.0.0.1","5432","zbx_monitor","zabbix"]
1
Time: 1.528 ms

But the trigger notifies:

PostgreSQL: Response too long (over 1s)

I am having the exact same issue,

Zabbix 4.4.7
Postgresql 10.6

It would be really helpful to get a response from the developers on this as this is an official template.

[Victor Sudakov]

Dear Colleagues,

Zabbix 5.0, Postgres 12.3 Debian. I've installed the official PostgreSQL template from Git. It is mostly working, that is, it has successfully discovered my databases and shows most of the DB statistics.

But for some reason some keys are not working. The database discovery created some items like pgsql.dbstat.blks_hit.rate["appinfo"] and pgsql.dbstat.deadlocks.rate["appinfo"], but they are reported as "Not supported" with the following error (example):

Preprocessing failed for: psql:/var/lib/appinfo/postgresql/pgsql.dbstat.sql:17: ОШИБКА: имя поля не может быть NULL
1. Failed: cannot extract value from json by path "$['appinfo'].blks_hit": cannot parse as a valid JSON object: invalid object format, expected opening character '{' or '[' at: 'psql:/var/lib/appinfo/postgresql/pgsql.dbstat.sql:17: ОШИБКА: имя поля не может быть NULL'

Sorry for copypasting Russian, in English the error should translate as "ERROR: field name cannot be NULL". It's evident that calling /var/lib/zabbix/postgresql/pgsql.dbstat.sql from the Zabbix agent causes a SQL error, but I'm not qualified enough to debug it. Or perhaps some macro is being expanded incorrectly. Please help.

[Victor Sudakov]

The database discovery created some items like pgsql.dbstat.blks_hit.rate["appinfo"] and pgsql.dbstat.deadlocks.rate["appinfo"], but they are reported as "Not supported" [...] Or perhaps some macro is being expanded incorrectly. Please help.

It actually seems something wrong with the /var/lib/zabbix/postgresql/pgsql.dbstat.sql script:

$ psql -qtAX -h "127.0.0.1" -p "5432" -U "zabbix" -d "zabbix" -c "select datname from pg_stat_database"

postgres
zabbix
template1
template0
appinfo

$ psql -qtAX -h "127.0.0.1" -p "5432" -U "zabbix" -d "zabbix" -f "/var/lib/zabbix/postgresql/pgsql.dbstat.sql"
psql:/var/lib/zabbix/postgresql/pgsql.dbstat.sql:17: ОШИБКА: имя поля не может быть NULL

[Victor Sudakov]

It actually seems something wrong with the /var/lib/zabbix/postgresql/pgsql.dbstat.sql script:

If anybody cares, it's because of https://rjuju.github.io/postgresql/2...ms-errors.html

You need to change pgsql.dbstat.sql :

diff --git a/templates/db/postgresql/postgresql/pgsql.dbstat.sql b/templates/db/postgresql/postgresql/pgsql.dbstat.sql
index d874658ccc..9a6e4c4de8 100644
--- a/templates/db/postgresql/postgresql/pgsql.dbstat.sql
+++ b/templates/db/postgresql/postgresql/pgsql.dbstat.sql
@@ -14,4 +14,4 @@ SELECT json_object_agg(datname, row_to_json(T)) FROM (
temp_files,
temp_bytes,
deadlocks
- FROM pg_stat_database) T
+ FROM pg_stat_database where datname is not null) T

[Victor Sudakov]

If anybody cares, it's because of https://rjuju.github.io/postgresql/2...ms-errors.html

You need to change pgsql.dbstat.sql :

diff --git a/templates/db/postgresql/postgresql/pgsql.dbstat.sql b/templates/db/postgresql/postgresql/pgsql.dbstat.sql
index d874658ccc..9a6e4c4de8 100644
--- a/templates/db/postgresql/postgresql/pgsql.dbstat.sql
+++ b/templates/db/postgresql/postgresql/pgsql.dbstat.sql
@@ -14,4 +14,4 @@ SELECT json_object_agg(datname, row_to_json(T)) FROM (
temp_files,
temp_bytes,
deadlocks
- FROM pg_stat_database) T
+ FROM pg_stat_database where datname is not null) T

Issue ZBX-17785 opened

[Siddhartha_gurijala]

Hello,
I was wondering if anyone is having issues with database discovery with postgresql 12. We used have a server with postgresql 10 in it and zabbix worked just fine on it but once we upgraded it to PG 12 we are no longer getting any data in latest data tab. Not sure what could have gone wrong. Any help would be appreciated.

[dig412]

One more problem:

zabbix_get -s 127.0.0.1 -k pgsql.ping.time["127.0.0.1","5432","zbx_monitor","zabbix"]
1
Time: 1.528 ms

But the trigger notifies:

PostgreSQL: Response too long (over 1s)

I found the easiest way to fix this was to change the unit on `pgsql.ping.time` to s (rather than ms), then add a preprocessing step, custom multiplier 0.001. This, turns the unit into a fractional second, rather than milliseconds.
Not sure why this is needed though, I'm sure zabbix should support ms units

[Victor Sudakov]

I found the easiest way to fix this was to change the unit on `pgsql.ping.time` to s (rather than ms), then add a preprocessing step, custom multiplier 0.001. This, turns the unit into a fractional second, rather than milliseconds.
Not sure why this is needed though, I'm sure zabbix should support ms units

I have opened https://support.zabbix.com/browse/ZBX-19073 for this. Please vote if you care.

[Bafff]

For those who are looking for xml template and not yaml you can find it here: https://github.com/zabbix/zabbix/tre.../db/postgresql